
package DAO;

import DTO.Address;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import Exception.*;

/**
 * This class represents abstraction for data access from address table in database.
 * This class is called from DAO as a proxy.
 * @see DAO
 * @version
 * @Date Mar 30, 2011
 * @author Tony Lei
 *
 */
public class AddressDAO {

    private Connection conn;
    private static final Logger logger = Logger.getLogger(AddressDAO.class.getName());

    /**
     * constructor for this class.
     * @param conn connection object
     */
    public AddressDAO(Connection conn) {
        this.conn = conn;
    }

    /**
     * This method inserts an address information into database .
     * @param aptNo a string representing apartment number
     * @param stNo an integer representing street number
     * @param streetName  a string representing street name
     * @param postCode a string representing postal code
     * @param city a string representing city
     * @param province  a string representing province
     * @return an integer
     * @throws KwickKioskDBException
     */
    public int addNewAddress(String aptNo, int stNo, String streetName,
            String postCode, String city, String province)
            throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "INSERT INTO address "
                    + "(aptNo, stNo, street, postCode, city, province) VALUES ("
                    + "'" + aptNo + "', "
                    + stNo + ", "
                    + "'" + streetName + "', "
                    + "'" + postCode + "', "
                    + "'" + city + "', "
                    + "'" + province + "')";
            if (updateDB(sqlText)) {
                sqlText = "select max(a.aid) from address a";
                ResultSet rs = sqlStatement.executeQuery(sqlText);
                if (rs.next()) {
                    int newAddressID = rs.getInt(1);
                    rs.close();
                    sqlStatement.close();
                    return newAddressID;
                } else {
                    rs.close();
                    sqlStatement.close();
                    conn.rollback();
                    return -1;
                }
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AddressDAO : addNewAddress" + ex);
            throw new KwickKioskDBException("AddressDAO : addNewAddress",
                        ex.getMessage());
        }
        return -1;
    }

    /**
     * This method updates customer address information into database
     * @param address data transfer object encapsulating address information
     * @return  boolean flag
     * @throws KwickKioskDBException
     */
    public boolean updateAddress(Address address)
            throws KwickKioskDBException {
        String sqlText = "update address set "
                + "stNo=" + address.getStNo() + ", "
                + "street='" + address.getStreet() + "', "
                + "postCode='" + address.getPostCode() + "', "
                + "city='" + address.getCity() + "', "
                + "province='" + address.getProvince() + "', ";

        if (address.getAptNo() == null) {
            sqlText += "aptNo=" + address.getAptNo() + " ";
        } else {
            sqlText += "aptNo='" + address.getAptNo() + "' ";
        }

        sqlText += "where aid=" + address.getID();

        return updateDB(sqlText);
    }

    /**
     * This method removes an address associated with customer from database
     * @param aid an integer representing address id
     * @return  boolean flag
     * @throws KwickKioskDBException
     */
    public boolean removeAddress(int aid)
            throws KwickKioskDBException {

        String sqlText = "DELETE FROM address WHERE aid=" + aid;
        return updateDB(sqlText);
    }

    /**
     * this methods updates the database
     * @param sqlText a string for data manipulation  query
     * @return a boolean flag
     * @throws KwickKioskDBException
     */
    private boolean updateDB(String sqlText) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            int value = sqlStatement.executeUpdate(sqlText);
            conn.commit();
            sqlStatement.close();
            if (value == 1) {
                return true;
            }
        } catch (SQLException ex) {
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "AddressDAO : updateDB" + ex);
                throw new KwickKioskDBException("AddressDAO : updateDB",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "AddressDAO : updateDB" + ex2);
                throw new KwickKioskDBException("AddressDAO : updateDB",
                        ex2.getMessage());
            }
        }

        return false;
    }

    /**
     * this methods updates the database
     * @param sqlText a string for data manipulation  query
     * @return  a boolean flag
     * @throws KwickKioskDBException
     */
     private boolean updateDB1(String sqlText) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            int value = sqlStatement.executeUpdate(sqlText);
     //       conn.commit();
            sqlStatement.close();
            if (value == 1) {
                return true;
            }
        } catch (SQLException ex) {
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "AddressDAO : updateDB" + ex);
                throw new KwickKioskDBException("AddressDAO : updateDB",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "AddressDAO : updateDB" + ex2);
                throw new KwickKioskDBException("AddressDAO : updateDB",
                        ex2.getMessage());
            }
        }

        return false;
    }
}
